﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace AppTrans
{
    class ReportZPVoditelObject
    {
        public const string sql = @"
SELECT DictionaryDrivers.Name as Водитель
	,Report.CountWorks as Кол__во_Работ
	,Report.Nachisleno as Начислено
	,Report.Nyplacheno as Выплачено
	,Report.Nachisleno - Report.Nyplacheno as К__выдаче
	,DictionaryProject.Name as Проект
FROM
	(
	SELECT Work1.Driver as Driver
		,COALESCE(Work1.CountWork,0) as CountWorks
		,COALESCE(Work1.SalaryDriver,0) as Nachisleno
		,COALESCE(Transactions1.Summa,0) as Nyplacheno
		,Work1.Project as Project
	FROM 
			(SELECT Work.Driver
			 ,SUM(COALESCE(Work.CountWork,0)) as CountWork
			 --,SUM(COALESCE(Work.SalaryDriver,0)) as SalaryDriver
			 ,SUM(COALESCE(Work.SalaryDriver,0)*COALESCE(Work.CountWork,0)) as SalaryDriver
			 ,Work.Project
			FROM Work
			WHERE Work.Driver is not null
			GROUP BY Work.Driver, Work.Project) as Work1
		LEFT JOIN
			(SELECT Transactions.Driver
			,sum(COALESCE(Transactions.Summa,0)) as Summa
			,Transactions.Project
			FROM Transactions
			WHERE Transactions.Driver is not null and 
			Transactions.Category in (select Value from Settings where Code Like 'code_cat_racx_zp_driver%')
			GROUP BY Transactions.Driver, Transactions.Project) as Transactions1
		ON Work1.Driver = Transactions1.Driver and Work1.Project = Transactions1.Project
		
	UNION ALL
	
	SELECT Transactions1.Driver as Driver
		,COALESCE(Work1.CountWork,0) as CountWorks
		,COALESCE(Work1.SalaryDriver,0) as Nachisleno
		,COALESCE(Transactions1.Summa,0) as Nyplacheno
		,Transactions1.Project as Project
	FROM 
			(SELECT Transactions.Driver
			,sum(COALESCE(Transactions.Summa,0)) as Summa
			,Transactions.Project
			FROM Transactions
			WHERE Transactions.Driver is not null and 
			Transactions.Category in (select Value from Settings where Code Like 'code_cat_racx_zp_driver%')
			GROUP BY Transactions.Driver, Transactions.Project) as Transactions1
		LEFT JOIN
			(SELECT Work.Driver
			,SUM(COALESCE(Work.CountWork,0)) as CountWork
			--,SUM(COALESCE(Work.SalaryDriver,0)) as SalaryDriver
			,SUM(COALESCE(Work.SalaryDriver,0)*COALESCE(Work.CountWork,0)) as SalaryDriver
			,Work.Project
			FROM Work
			WHERE Work.Driver is not null
			GROUP BY Work.Driver, Work.Project) as Work1
		ON Transactions1.Driver = Work1.Driver and Transactions1.Project = Work1.Project
		WHERE Work1.Driver is null and Work1.SalaryDriver is null and Work1.Project is null
	) as Report
LEFT JOIN DictionaryDrivers ON DictionaryDrivers.ID = Report.Driver
LEFT JOIN DictionaryProject ON DictionaryProject.ID = Report.Project
ORDER BY Report.Driver, Report.Project
";
        public string Водитель { get; set; }
        public int Кол__во_Работ { get; set; }
        public decimal? Начислено { get; set; }
        public decimal? Выплачено { get; set; }
        public decimal? К__выдаче { get; set; }
        public string Проект { get; set; }

    }
}
